with  tab_route_zhong as (
    select first_network_code     --始发机构编码
         , first_network_name     --始发机构名称
         , last_network_code      --目的机构编码
         , last_network_name      --目的机构名称
         , planned_departure_time --规划发车时间
         , planned_arrival_time   --规划到车时间
         , total_days             --T+时效
         , dt
    from (
             select case
                        when substr_route_type = '中心-网点' then in_to_code
                        when substr_route_type = '集散-网点' then in_collect_code
                 end                               as first_network_code     --始发机构
                  , case
                        when substr_route_type = '中心-网点' then in_to_name
                        when substr_route_type = '集散-网点' then in_collect_name
                 end                               as first_network_name
                  , out_to_code                    as last_network_code      --目的网点
                  , out_to_name                    as last_network_name
                  , case
                        when substr_route_type = '中心-网点'
                            then nvl(e1_planned_departure, out_edge2_planned_departure) --始发中心规划发车时间
                        when substr_route_type = '集散-网点' then coalesce(in_edge2_planned_departure, e1_planned_departure,
                                                                       out_edge2_planned_departure) --始发集散规划发车时间
                 end                               as planned_departure_time --规划发车时间
                  , out_edge2_planned_arrival_time as planned_arrival_time   --规划到车时间
                  , search_type                    as total_days             --T+时效
                  , dt
                  , row_number()                      over(partition by case when substr_route_type = '中心-网点' then in_to_code
                                             when substr_route_type = '集散-网点' then in_collect_code end
                                       ,out_to_code
                                       ,case when substr_route_type = '中心-网点' then nvl(e1_planned_departure,out_edge2_planned_departure)   --始发中心规划发车时间
                                             when substr_route_type = '集散-网点' then coalesce(in_edge2_planned_departure,e1_planned_departure,out_edge2_planned_departure) end
                                       ,dt
                               order by search_type asc
                                       ,total_time_use asc) as rn
             from jms_dm.dm_route_whole_substr_base_dt
             where dt = date_add(substr('{{ execution_date | cst_hour }}',1,10),-2)
               and substr_route_type in ('中心-网点', '集散-网点')
         ) a
    where a.rn = 1
    union all
    select in_from_code                                                                           as first_network_code
         , in_from_name                                                                           as first_network_name
         , out_to_code                                                                            as last_network_code
         , out_to_name                                                                            as last_network_name
         , coalesce(in_edge_planned_departure, e1_planned_departure,
                    out_edge2_planned_departure)                                                  as planned_departure_time --规划发车时间  (补充网点直发中心场景)
         , nvl(out_edge2_planned_arrival_time, in_edge_planned_arrival)                           as planned_arrival_time   --规划到车时间  补充网点-网点到车时间
         , search_type                                                                            as total_days             --T+时效
         , dt
    from jms_dm.dm_route_recommend --网点-网点
    where dt = date_add(substr('{{ execution_date | cst_hour }}',1,10),-2)
      and in_from_name is not null
      and out_to_code is not null --剔除半截路由
    group by in_from_code
           , in_from_name
           , out_to_code
           , out_to_name
           , coalesce(in_edge_planned_departure, e1_planned_departure, out_edge2_planned_departure)
           , nvl(out_edge2_planned_arrival_time, in_edge_planned_arrival)
           , search_type
           , dt
    distribute by dt
   , pmod(hash(first_network_code)
   , 10)
    )
----------------------------------------------------------------------------------------------------------------------------------------------------------
insert overwrite table bidefault.dm_forecast_network_deliver_volume_detail_dt partition(dt)
select
    tt.waybill_no    --运单号
     ,tt.scan_time  as send_time  --发件时间
     ,tt.scan_site_code as send_site_code --发件网点code
     ,net1.name as send_site_name --发件网点name
     ,tt.dispatch_network_code --派件网点code
     ,net2.name as dispatch_network_name --派件网点name
     ,tt.first_code  --一段码
     ,tt.second_code --二段码
     ,tt.center_code as dest_center_code--目的中心code
     ,tt.center_name as dest_center_name--目的中心name
     ,tt.planned_departure_time  --规划发车时间
     ,tt.planned_arrival_time  --规划到件时间
     ,tt.total_days --时效天
     --,concat(date_add(date(tt.scan_time),tt.total_days),' ',tt.planned_arrival_time,':00') as dest_arrival_time --目的网点规划到件时间
     ,case when planned_departure_time>=date_format(tt.scan_time,'HH:mm')
               then concat(date_add(date(tt.scan_time),tt.total_days),' ',tt.planned_arrival_time,':00')
           when planned_departure_time<date_format(tt.scan_time,'HH:mm')
               then concat(date_add(date(tt.scan_time),tt.total_days+1),' ',tt.planned_arrival_time,':00')
    end as dest_arrival_time
     ,tt.dt as date_time
     ,tt.dt --时间分区
from
    (
        select
            scan.waybill_no
             ,scan.scan_time
             ,scan.scan_site_code
             ,oro.dispatch_network_code
             ,oro.first_code
             ,oro.second_code
             ,oro.third_code
             ,sys.center_code
             ,sys.center_name
             ,route.planned_departure_time
             ,route.planned_arrival_time
             ,route.total_days
             ,row_number() over(partition by scan.waybill_no,scan.scan_time order by
    case when planned_departure_time>=date_format(scan.scan_time,'HH:mm') 
         then unix_timestamp(concat(current_date(),' ',planned_departure_time,':00'), 'yyyy-MM-dd HH:mm:ss') -unix_timestamp(scan.scan_time, 'yyyy-MM-dd HH:mm:ss') 
         when planned_departure_time<date_format(scan.scan_time,'HH:mm')
         then unix_timestamp(concat(date_add(current_date(),1),' ',planned_departure_time,':00'), 'yyyy-MM-dd HH:mm:ss') -unix_timestamp(scan.scan_time, 'yyyy-MM-dd HH:mm:ss') end ,total_days,planned_arrival_time
        ) as rk
    ,scan.dt
        from
            (
                select
                    waybill_no
                     ,scan_time
                     ,scan_site_code
                     ,row_number() over(partition by waybill_no order by scan_time desc) as rk
        ,dt
                from
                    (
                        select
                            waybill_no ,scan_time ,scan_site_code,dt
                        from jms_dwd.dwd_tab_barscan_send_base_hi
                        where dt='{{ execution_date | cst_hour }}'
                         --     substr(dt,1,10) between '2023-12-08' and '2023-12-08'
                        union all
                        select
                            waybill_no ,scan_time ,scan_site_code,dt
                        from jms_dwd.dwd_tab_barscan_taking_base_hi
                        where dt='{{ execution_date | cst_hour }}'
                          --    substr(dt,1,10) between '2023-12-08' and '2023-12-08'
                    )t
            )scan
                inner join jms_dwd.dwd_yl_oms_oms_order_hf oro
                           on oro.dt between concat(date_add(substr('{{ execution_date | cst_hour }}',1,10),-15),'-00')  and '{{ execution_date | cst_hour }}'
                               and oro.waybill_rnk=1 and oro.waybill_id = scan.waybill_no
                               and scan.rk=1
                left join jms_dim.dim_lmdm_sys_first_code sys on sys.code=oro.first_code
                left join tab_route_zhong route on  scan.scan_site_code=route.first_network_code and oro.dispatch_network_code=route.last_network_code
    )tt left join jms_dim.dim_network_whole_massage net1 on net1.code=tt.scan_site_code
        left join jms_dim.dim_network_whole_massage net2 on net2.code=tt.dispatch_network_code
where rk=1
    distribute by dt,pmod(hash(waybill_no), 4);
------------------------------
insert overwrite table bidefault.dm_forecast_network_deliver_volume_cnt_dt partition(dt='{{ execution_date | cst_hour }}')
select
    dispatch_network_code,dispatch_network_name,dest_arrival_time,count(1) as waybill_cnt
from
    (
        select
            waybill_no,dispatch_network_code,dispatch_network_name,dest_arrival_time,dt,
            row_number() over(partition by waybill_no order by dt) as rk
        from bidefault.dm_forecast_network_deliver_volume_detail_dt
        where dt between concat(date_add(substr('{{ execution_date | cst_hour }}',1,10),-7),'-00')  and '{{ execution_date | cst_hour }}'
          and dest_arrival_time is not null
    )t where rk=1
group by dest_arrival_time,dispatch_network_code,dispatch_network_name
    distribute by pmod(hash(dispatch_network_code), 4);
